package Service.impl;

import View.Database;
import Request.StudentRequest;
import Reservice.TableDTO;
import Service.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class Studen implements Student {
    @Override
    public TableDTO retrieveStudents(StudentRequest requext) {
        StringBuilder sql = new StringBuilder();
        sql.append("select*from evaluate ");
        if (requext.getSearchKey() != null && !"".equals(requext.getSearchKey().trim())) {
            sql.append(" WHERE 姓名 like'%" + requext.getSearchKey().trim() + "%'");
        }
        sql.append("order by id desc limit ").append(requext.getStart()).append(",")
                .append(requext.getPageSize());
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TableDTO returnDTO = new TableDTO();
//        Vector<Vector<Object>> data=new Vector<>();
        try {
            conn = Database.getConn();
//            System.out.println(sql.toString());
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();

            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*)from evaluate");//数据库查询
            if (requext.getSearchKey() != null && !"".equals(requext.getSearchKey().trim())) {
                sql.append(" WHERE 姓名 like'%" + requext.getSearchKey().trim() + "%'");//模糊查询
            }
            ps=conn.prepareStatement(sql.toString());
            rs= ps.executeQuery();

            while (rs.next()){
//                Vector<Object> one=new Vector<>();
                int count = rs.getInt(1);
                returnDTO.setTotalCont(count);
            }
            return returnDTO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Database.closeRs(rs);
            Database.closePs(ps);
            Database.closeConn(conn);
        }

        return null;
    }

    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            Vector<Object> oneRecord = new Vector<>();
//            int id = rs.getInt("id");
            String gh = rs.getString("学号");
            String xm = rs.getString("密码");
            String xb = rs.getString("姓名");
            String lxfs = rs.getString("面向对象");
            String py = rs.getString("py程序设计");
            String c = rs.getString("c语言");
//            oneRecord.addElement(id);
            oneRecord.addElement(gh);
            oneRecord.addElement(xm);
            oneRecord.addElement(xb);
            oneRecord.addElement(lxfs);
            oneRecord.addElement(py);
            oneRecord.addElement(c);
            data.addElement(oneRecord);
        }
        return data;
    }
}
